﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for SanPhamCtr
/// </summary>
public class SanPhamCtr
{
    SqlDataAdapter da;
    DataSet ds;
    DataProvider con = new DataProvider();
	public SanPhamCtr()
	{
	}
    public DataTable CountSanPham(string masp)
    {
        string sql = "select soluong from sanpham where masp=@masp";
        return DataProvider.GetData(sql,"@masp",masp);
       
    }
    public bool UpdateLuotXemSP(string MaSP)
    {
        try
        {
            DataProvider.ExecuteNonQuery("Update SanPham set LuotXemSP=LuotXemSP + 1 Where MaSP=@MaSP","@MaSP",MaSP);
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }
    public DataTable GetSanPhamXemNhieu()
    {
        try
        {
            return DataProvider.GetData("SELECT top 3 *  FROM SanPham WHERE TrangThai='True' ORDER BY LuotXemSP DESC  ");
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable layMaSanPhamTrongCTPhieuNhap(string masp)
    {
        string sql = "select * from ChiTietPhieuNhap where masp=@masp";
        return DataProvider.GetData(sql, "@masp", masp);

    }
    public DataTable GetSanPhamTheoMaDanhMuc(string MaDanhMuc)
    {
        try
        {
            //return DataProvider.GetData("Select SanPham.* FROM SanPham, PhanLoai,DanhMuc WHERE SanPham.LoaiSP=PhanLoai.LoaiSP and DanhMuc.MaDanhMuc= PhanLoai.MaDanhMuc and DanhMuc.MaDanhMuc=@MaDanhMuc and SanPham.TrangThai='True' ", "@MaDanhMuc", MaDanhMuc);
            string sql="Select sp.*,round(avg(Diem),0) as Diem FROM SanPham sp,Danhgia dg, PhanLoai,DanhMuc WHERE sp.LoaiSP=PhanLoai.LoaiSP and DanhMuc.MaDanhMuc= PhanLoai.MaDanhMuc and DanhMuc.MaDanhMuc=@MaDanhMuc and sp.TrangThai='True' and sp.masp=dg.masp and dg.trangthai=1 group by dg.MaSP,sp.masp,sp.tensp,sp.loaisp,sp.gia,sp.soluong,sp.ngaydang,sp.hinhanh,sp.noisx,sp.chitietsp,sp.danhchodotuoi,sp.trangthai,sp.danhchogioitinh,sp.luotxemsp ";
            sql+="union Select sp.*,0 as Diem FROM SanPham sp, PhanLoai,DanhMuc WHERE sp.LoaiSP=PhanLoai.LoaiSP and DanhMuc.MaDanhMuc= PhanLoai.MaDanhMuc and DanhMuc.MaDanhMuc=@MaDanhMuc and sp.TrangThai='True' and sp.masp not in(select masp from danhgia where trangthai=1) group by sp.masp,sp.tensp,sp.loaisp,sp.gia,sp.soluong,sp.ngaydang,sp.hinhanh,sp.noisx,sp.chitietsp,sp.danhchodotuoi,sp.trangthai,sp.danhchogioitinh,sp.luotxemsp";
            return DataProvider.GetData(sql,"@MaDanhMuc", MaDanhMuc);
            
        }
        catch
        {
            throw new Exception("URL không hợp lệ!");
        }
    }
    public DataTable GetDSSanPhamTheoMaDanhMuc(string MaDanhMuc)
    {
        try
        {
            return DataProvider.GetData("Select SanPham.*,TenLoaiSP FROM SanPham, PhanLoai WHERE SanPham.LoaiSP=PhanLoai.LoaiSP and PhanLoai.MaDanhMuc=@MaDanhMuc and SanPham.TinhTrang='True' ", "@MaDanhMuc", MaDanhMuc);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public DataTable GetSanPhamCungLoai(string masp,string loaisp)
    {
        try
        {
            return DataProvider.GetData("select top 5 sp.* from sanpham sp where loaisp=@loaisp and masp not in(select masp from sanpham where loaisp=@loaisp and masp=@masp) and trangthai='true' ORDER BY sp.NgayDang DESC","@masp",masp,"@loaisp",loaisp);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public ESanPham GetSanPhamPD(string masp, string loaisp)
    {
        try
        {
            DataTable tableSanPham = DataProvider.GetData("SELECT * FROM SanPham WHERE MaSP=@masp and LoaiSP=@loaisp and TrangThai='True' ORDER BY NgayDang DESC", "@masp", masp, "@loaisp", loaisp);
            ESanPham sanPham = new ESanPham();
            sanPham.MaSP = tableSanPham.Rows[0]["MaSP"].ToString();
            sanPham.TenSP = tableSanPham.Rows[0]["TenSP"].ToString();
            sanPham.SoLuong = Int32.Parse(tableSanPham.Rows[0]["SoLuong"].ToString());
            sanPham.NgayDang = DateTime.Parse(tableSanPham.Rows[0]["NgayDang"].ToString());
            sanPham.HinhAnh = tableSanPham.Rows[0]["HinhAnh"].ToString();
            sanPham.Gia = Int32.Parse(tableSanPham.Rows[0]["Gia"].ToString());
            sanPham.LoaiSP = Int32.Parse(tableSanPham.Rows[0]["LoaiSP"].ToString());
            sanPham.NoiSX = tableSanPham.Rows[0]["NoiSX"].ToString();
            sanPham.ChiTietSP = tableSanPham.Rows[0]["ChiTietSP"].ToString();
            sanPham.DanhChoDoTuoi = Int32.Parse(tableSanPham.Rows[0]["DanhChoDoTuoi"].ToString());
            sanPham.DanhChoGioiTinh = tableSanPham.Rows[0]["DanhChoGioiTinh"].ToString();
            sanPham.TrangThai = tableSanPham.Rows[0]["TrangThai"].ToString();
            sanPham.LuotXemSP = Int32.Parse(tableSanPham.Rows[0]["LuotXemSP"].ToString());
            return sanPham;
        }
        catch
        {
            throw new Exception("URL không hợp lệ!");
        }
    }
    public ESanPham GetSanPham(string MaSanPham)
    {
        try
        {
            DataTable tableSanPham = DataProvider.GetData("SELECT * FROM SanPham WHERE MaSP=@MaSanPham and TrangThai='True' ORDER BY NgayDang DESC", "@MaSanPham", MaSanPham);
            ESanPham sanPham = new ESanPham();
            sanPham.MaSP = tableSanPham.Rows[0]["MaSP"].ToString();
            sanPham.TenSP = tableSanPham.Rows[0]["TenSP"].ToString();
            sanPham.SoLuong = Int32.Parse(tableSanPham.Rows[0]["SoLuong"].ToString());
            sanPham.NgayDang = DateTime.Parse(tableSanPham.Rows[0]["NgayDang"].ToString());
            sanPham.HinhAnh = tableSanPham.Rows[0]["HinhAnh"].ToString();
            sanPham.Gia = Int32.Parse(tableSanPham.Rows[0]["Gia"].ToString());
            sanPham.LoaiSP = Int32.Parse(tableSanPham.Rows[0]["LoaiSP"].ToString());
            sanPham.NoiSX = tableSanPham.Rows[0]["NoiSX"].ToString();
            sanPham.ChiTietSP = tableSanPham.Rows[0]["ChiTietSP"].ToString();
            sanPham.DanhChoDoTuoi = Int32.Parse(tableSanPham.Rows[0]["DanhChoDoTuoi"].ToString());
            sanPham.DanhChoGioiTinh = tableSanPham.Rows[0]["DanhChoGioiTinh"].ToString();
            sanPham.TrangThai = tableSanPham.Rows[0]["TrangThai"].ToString();
            sanPham.LuotXemSP = Int32.Parse(tableSanPham.Rows[0]["LuotXemSP"].ToString());
            return sanPham;
        }
        catch
        {
            throw new Exception("URL không hợp lệ!");
        }
    }

    public ESanPham GetDSSanPham(string LoaiSP)
    {
        try
        {
            DataTable tableSanPham = DataProvider.GetData("SELECT SanPham.* FROM SanPham, PhanLoai WHERE SanPham.TrangThai='True' and SanPham.LoaiSP=PhanLoai.LoaiSP and PhanLoai.LoaiSP=@LoaiSP ORDER BY SanPham.NgayDang DESC", "@LoaiSP", LoaiSP);
            ESanPham sanPham = new ESanPham();
            sanPham.MaSP = tableSanPham.Rows[0]["MaSP"].ToString();
            sanPham.TenSP = tableSanPham.Rows[0]["TenSP"].ToString();
            sanPham.SoLuong = Int32.Parse(tableSanPham.Rows[0]["SoLuong"].ToString());
            sanPham.NgayDang = DateTime.Parse(tableSanPham.Rows[0]["NgayDang"].ToString());
            sanPham.HinhAnh = tableSanPham.Rows[0]["HinhAnh"].ToString();
            sanPham.Gia = Int32.Parse(tableSanPham.Rows[0]["Gia"].ToString());
            sanPham.LoaiSP = Int32.Parse(tableSanPham.Rows[0]["LoaiSP"].ToString());
            sanPham.NoiSX = tableSanPham.Rows[0]["NoiSX"].ToString();
            sanPham.ChiTietSP = tableSanPham.Rows[0]["ChiTietSP"].ToString();
            sanPham.DanhChoDoTuoi = Int32.Parse(tableSanPham.Rows[0]["DanhChoDoTuoi"].ToString());
            sanPham.DanhChoGioiTinh = tableSanPham.Rows[0]["DanhChoGioiTinh"].ToString();
            sanPham.TrangThai = tableSanPham.Rows[0]["TrangThai"].ToString();
          

            return sanPham;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    //public bool InsertSanPham(ESanPham SanPham)
    //{
    //    try
    //    {
    //        DataProvider.ExecuteNonQuery("INSERT INTO SanPham VAlUES(@MaSP,@TenSP,@LoaiSP,@Gia,@SoLuong,@NgayDang,@HinhAnh,@NoiSX,@ChiTietSP,@DoTuoi,@TinhTrang)",
    //            "@MaSP", SanPham.MaSP, "@TenSP", SanPham.TenSP, "@LoaiSP", SanPham.LoaiSP, "@Gia", SanPham.Gia, "@SoLuong", SanPham.SoLuong, "@NgayDang", SanPham.NgayDang, "@HinhAnh", SanPham.HinhAnh, "@NoiSX", SanPham.NoiSX, "@ChiTietSP", "@DoTuoi",SanPham.DoTuoi, SanPham.ChiTietSP, "@TinhTrang", SanPham.TrangThai);
    //        return true;
    //    }
    //    catch (Exception ex)
    //    {
    //        throw ex;
    //    }
    //}
    public DataTable GetSanPham()
    {
        try
        {
            return DataProvider.GetData("SELECT *,TenLoaiSP  FROM SanPham,PhanLoai WHERE SanPham.LoaiSP=PhanLoai.LoaiSP and SanPham.TrangThai='True' ORDER BY NgayDang DESC  ");
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable GetSanPhamNgauNhien()
    {
        try
        {
            return DataProvider.GetData("SELECT TOP 3 * FROM SanPham ORDER BY NEWID()");
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable GetSanPhamMoiNhat()
    {
        try
        {
            return DataProvider.GetData("SELECT TOP 1 * FROM SanPham ORDER BY NgayDang DESC ");
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable GetSanPham1(string pCot)
    {
        try
        {
            return DataProvider.GetData("SELECT *,TenLoaiSP as TenLSP  FROM SanPham,PhanLoai WHERE SanPham.LoaiSP=PhanLoai.LoaiSP and TrangThai='True' ORDER BY " + pCot);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable GetDSSanPhamTheoMa(string LoaiSP)
    {
        try
        {
            string sql = "Select sp.*,round(avg(Diem),0) as Diem FROM SanPham sp,Danhgia dg, PhanLoai WHERE sp.LoaiSP=PhanLoai.LoaiSP and PhanLoai.LoaiSP=@LoaiSP and sp.TrangThai='True' and sp.masp=dg.masp and dg.trangthai=1 group by dg.MaSP,sp.masp,sp.tensp,sp.loaisp,sp.gia,sp.soluong,sp.ngaydang,sp.hinhanh,sp.noisx,sp.chitietsp,sp.danhchodotuoi,sp.trangthai,sp.danhchogioitinh,sp.luotxemsp ";
            sql += "union Select sp.*,0 as Diem FROM SanPham sp, PhanLoai WHERE sp.LoaiSP=PhanLoai.LoaiSP and PhanLoai.LoaiSP=@LoaiSP and sp.TrangThai='True' and sp.masp not in(select masp from danhgia where trangthai=1) group by sp.masp,sp.tensp,sp.loaisp,sp.gia,sp.soluong,sp.ngaydang,sp.hinhanh,sp.noisx,sp.chitietsp,sp.danhchodotuoi,sp.trangthai,sp.danhchogioitinh,sp.luotxemsp";
            //return DataProvider.GetData("Select SanPham.* FROM SanPham, PhanLoai WHERE SanPham.LoaiSP=PhanLoai.LoaiSP and PhanLoai.LoaiSP=@LoaiSP and SanPham.TrangThai='True' ORDER BY SanPham.NgayDang DESC ", "@LoaiSP", LoaiSP);
            return DataProvider.GetData(sql, "@LoaiSP", LoaiSP);
        }
        catch
        {
            throw new Exception("URL không hợp lệ!");
        }
    }
   
    public DataTable GetSanPhamMoi()
    {
        try
        {
            return DataProvider.GetData("SELECT top 6 *  FROM SanPham WHERE TrangThai='True' ORDER BY NgayDang DESC  ");
           // return DataProvider.GetData("SELECT top 6 sp.*,round(avg(Diem),0) as Diem FROM SanPham sp,DanhGia dg WHERE sp.TrangThai='True' and sp.masp=dg.masp and dg.trangthai=1 group by dg.MaSP,sp.masp,sp.tensp,sp.loaisp,sp.gia,sp.soluong,sp.ngaydang,sp.hinhanh,sp.noisx,sp.chitietsp,sp.danhchodotuoi,sp.trangthai,sp.danhchogioitinh,sp.luotxemsp ORDER BY sp.NgayDang DESC  ");
            
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public bool DeleteSanPham(string MaSP)
    {
        try
        {
            DataProvider.ExecuteNonQuery("Update SanPham Set TrangThai=1 Where MaSP=@MaSP", "@MaSP", MaSP);
            return true;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    //public bool UpdateSanPham(ESanPham SanPham)
    //{
    //    try
    //    {
    //        DataProvider.ExecuteNonQuery("Update SanPham set TenSP=@TenSP,LoaiSP=@LoaiSP,Gia=@Gia,SoLuong=@SoLuong,NgayDang=@NgayDang,HinhAnh=@HinhAnh,NoiSX=@NoiSX,ChiTietSP=@ChiTietSP,DoTuoi=@DoTuoi,TinhTrang=@TinhTrang where MaSP=@MaSP",
    //         "@TenSP", SanPham.TenSP, "@LoaiSP", SanPham.LoaiSP, "@Gia", SanPham.Gia, "@SoLuong", SanPham.SoLuong, "@NgayDang", SanPham.NgayDang, "@HinhAnh", SanPham.HinhAnh, "@NoiSX", SanPham.NoiSX, "@ChiTietSP", SanPham.ChiTietSP,"@DoTuoi",SanPham.DoTuoi, "@TinhTrang", SanPham.TrangThai, "@MaSP", SanPham.MaSP);
    //        return true;
    //    }
    //    catch (Exception ex)
    //    {
    //        throw ex;
    //    }
    //}
    public DataTable LaySanPhamLienQuan(string LoaiSP)
    {
        try
        {
            string sql = "select top 6 MaSP,TenSP from SanPham where LoaiSP=@LoaiSP order by MaSP DESC";
            return DataProvider.GetData(sql, "@a", LoaiSP);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    public DataTable GetMaSanPham()
    {
        try
        {
            return DataProvider.GetData("Select MAx(MaSP) as MaSP from SanPham");
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    //Hanh
    public bool InsertSanPhamH(ESanPham SanPham)
    {
        try
        {
            DataProvider.ExecuteNonQuery("INSERT INTO SanPham VAlUES(@MaSP,@TenSP,@LoaiSP,@Gia,@SoLuong,@NgayDang,@HinhAnh,@NoiSX,@ChiTietSP,@DanhChoDoTuoi,@TinhTrang,@DanhChoGioiTinh,'0')","@MaSP",SanPham.MaSP, "@TenSP", SanPham.TenSP, "@LoaiSP", SanPham.LoaiSP, "@Gia", SanPham.Gia, "@SoLuong", SanPham.SoLuong, "@NgayDang", SanPham.NgayDang, "@HinhAnh", SanPham.HinhAnh, "@NoiSX", SanPham.NoiSX, "@ChiTietSP", SanPham.ChiTietSP, "@DanhChoDoTuoi", SanPham.DanhChoDoTuoi, "@DanhChoGioiTinh", SanPham.DanhChoGioiTinh, "@TinhTrang", SanPham.TrangThai);
            return true;
        }
        catch (Exception ex)
        {
            //throw ex;
            return false;
        }
    }
    public ESanPham GetSanPhamTheoTenH(string TenSanPham)
    {
        try
        {
            DataTable tableSanPham = DataProvider.GetData("SELECT * FROM SanPham WHERE TenSP like @MaSanPham", "@MaSanPham", TenSanPham);
            ESanPham sanPham = new ESanPham();
            sanPham.MaSP = tableSanPham.Rows[0]["MaSP"].ToString();
            sanPham.TenSP = tableSanPham.Rows[0]["TenSP"].ToString();
            sanPham.SoLuong = Int32.Parse(tableSanPham.Rows[0]["SoLuong"].ToString());
            sanPham.NgayDang = DateTime.Parse(tableSanPham.Rows[0]["NgayDang"].ToString());
            sanPham.HinhAnh = tableSanPham.Rows[0]["HinhAnh"].ToString();
            sanPham.Gia = Int32.Parse(tableSanPham.Rows[0]["Gia"].ToString());
            sanPham.LoaiSP = Int32.Parse(tableSanPham.Rows[0]["LoaiSP"].ToString());
            sanPham.NoiSX = tableSanPham.Rows[0]["NoiSX"].ToString();
            sanPham.ChiTietSP = tableSanPham.Rows[0]["ChiTietSP"].ToString();
            sanPham.DanhChoDoTuoi = Int32.Parse(tableSanPham.Rows[0]["DanhChoDoTuoi"].ToString());
            sanPham.DanhChoGioiTinh = tableSanPham.Rows[0]["DanhChoGioiTinh"].ToString();
            sanPham.TrangThai = tableSanPham.Rows[0]["TrangThai"].ToString();
            return sanPham;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public bool UpdateSanPhamH(ESanPham SanPham)
    {
        try
        {
            DataProvider.ExecuteNonQuery("Update SanPham set TenSP=@TenSP,LoaiSP=@LoaiSP,Gia=@Gia,SoLuong=@SoLuong,NgayDang=@NgayDang,HinhAnh=@HinhAnh,NoiSX=@NoiSX,ChiTietSP=@ChiTietSP,DanhChoDoTuoi=@DanhChoDoTuoi,DanhChoGioiTinh=@DanhChoGioiTinh,TrangThai=@TinhTrang where MaSP=@MaSP",
             "@TenSP", SanPham.TenSP, "@LoaiSP", SanPham.LoaiSP, "@Gia", SanPham.Gia, "@SoLuong", SanPham.SoLuong, "@NgayDang", SanPham.NgayDang, "@HinhAnh", SanPham.HinhAnh, "@NoiSX", SanPham.NoiSX, "@ChiTietSP", SanPham.ChiTietSP,"@DanhChoDoTuoi",SanPham.DanhChoDoTuoi,"@DanhChoGioiTinh",SanPham.DanhChoGioiTinh,"@TinhTrang", SanPham.TrangThai, "@MaSP", SanPham.MaSP);
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }
    public DataTable GetSanPhamH()
    {
        try
        {
            return DataProvider.GetData("SELECT *,TenLoaiSP  FROM SanPham,PhanLoai WHERE SanPham.LoaiSP=PhanLoai.LoaiSP and SanPham.TrangThai='True' ORDER BY masp DESC");
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public int countProduct()
    {
        String sql = "Select Count(*) From SanPham where TrangThai=1";
        da = new SqlDataAdapter(sql, DataProvider.GetConnection());
        ds = new DataSet();
        da.Fill(ds);
        return int.Parse(ds.Tables[0].Rows[0][0].ToString());
    }
    public DataTable GetSanPham1H(string pCot)
    {
        try
        {
            return DataProvider.GetData("SELECT *,TenLoaiSP as TenLSP  FROM SanPham,PhanLoai WHERE SanPham.LoaiSP=PhanLoai.LoaiSP and SanPham.TrangThai='True' ORDER BY " + pCot);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public bool DeleteSanPhamH(string MaSP)
    {
        try
        {
            DataProvider.ExecuteNonQuery("Update SanPham Set TrangThai=0 Where MaSP=@MaSP", "@MaSP", MaSP);
            return true;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public ESanPham GetSanPhamH(string MaSanPham)
    {
        try
        {
            DataTable tableSanPham = DataProvider.GetData("SELECT * FROM SanPham WHERE MaSP=@MaSanPham and TrangThai='True'", "@MaSanPham", MaSanPham);
            ESanPham sanPham = new ESanPham();
            sanPham.MaSP = tableSanPham.Rows[0]["MaSP"].ToString();
            sanPham.TenSP = tableSanPham.Rows[0]["TenSP"].ToString();
            sanPham.SoLuong = Int32.Parse(tableSanPham.Rows[0]["SoLuong"].ToString());
            sanPham.NgayDang = DateTime.Parse(tableSanPham.Rows[0]["NgayDang"].ToString());
            sanPham.HinhAnh = tableSanPham.Rows[0]["HinhAnh"].ToString();
            sanPham.Gia = Int32.Parse(tableSanPham.Rows[0]["Gia"].ToString());
            sanPham.LoaiSP = Int32.Parse(tableSanPham.Rows[0]["LoaiSP"].ToString());
            sanPham.NoiSX = tableSanPham.Rows[0]["NoiSX"].ToString();
            sanPham.ChiTietSP = tableSanPham.Rows[0]["ChiTietSP"].ToString();
            sanPham.DanhChoDoTuoi = Int32.Parse(tableSanPham.Rows[0]["DanhChoDoTuoi"].ToString());
            sanPham.DanhChoGioiTinh = tableSanPham.Rows[0]["DanhChoGioiTinh"].ToString();
            sanPham.TrangThai = tableSanPham.Rows[0]["TrangThai"].ToString();
            return sanPham;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

   
}